Write an Sql expression to directly query a database. You can load the result set into variables, and then use those variable at another stage in the flow.
SQL Script Properties
Display Name
The default display name of the for loop node is SQL Script 1. Any subsequent for loop nodes are named according to this naming convention, with the appropriate numeric suffix, e.g. 'SQL Script 2', ' SQL Script 3', etc.
You can change the SQL script node name from directly from this field.
Description
You have the option to add a description to each for loop node; this can be a useful way of documenting the node for yourself and other users.
Server
Choose the server where the relevant database is stored.
Database
Choose the database that you want to query.
SQL Script
Write your SQL script in the script window. You can open the SQL Editor which exposes the data source's schemas; you can easily add tables and columns to the SQL statement by double clicking or dragging and dropping. The editor also exposes any variables that have been configured in the Data Flow, enabling you to inject variables into the SQL statement.
Click the SQL icon to open the SQL Editor (red arrow below).
Validate
Validate the SQL script.
Load into Variables
Load the result set (green highlight below) of the SQL script into the given variable (red highlight below).
You can create a list variable (which are used in For Each Loops) by using the union select function to select multiple values.
select 'Sydney' union select 'Brisbane' union select 'Melbourne'
- Click here to learn more about variables.
Example
In the example below, a list variable is created containing the cities Sydney, Brisbane, and Melbourne:
